package jdbcTest2;

// 查询计算机分类下的图书借阅信息

import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;

import javax.sql.DataSource;
import java.sql.*;
import java.util.Scanner;

public class Select {
    public static void main(String[] args) throws SQLException {
        // 创建数据源
        DataSource dataSource = new MysqlDataSource();
        ((MysqlDataSource)dataSource).setURL("jdbc:mysql://127.0.0.1:3306/ebook?characterEncoding=utf8&useSSL=false");
        ((MysqlDataSource)dataSource).setUser("root");
        ((MysqlDataSource)dataSource).setPassword("Xianmeng");

        // 建立连接
        Connection connection = dataSource.getConnection();

        // 构造SQL + 预处理
        Scanner in = new Scanner(System.in);
        System.out.println("输入想要查询的分类名称：");
        String name1 = in.next();

        // 根据分类找id  ——category中对应的id
        String sql1 = "select id from category where name = '" + name1 + "' ";
        PreparedStatement statement1 = connection.prepareStatement(sql1);
        int cateId = 0;
        ResultSet resultSet1 = statement1.executeQuery();
        while (resultSet1.next()) {
            cateId = resultSet1.getInt("id");
        }
        System.out.println(statement1);
        System.out.println(cateId);

        // 找到了类型id，再找图书id
        String sql2 = "select id from book where  category_id = " + cateId + " ";
        PreparedStatement statement2 = connection.prepareStatement(sql2);
        int bookId = 0;
        ResultSet resultSet2 = statement2.executeQuery();
        while (resultSet2.next()) {
            bookId = resultSet2.getInt("id");
            String sql = "select * from borrow_info where book_id = ?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setInt(1,bookId);

            // 执行 + 遍历
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                // id  book_id student_id start_time end_time
                int id = resultSet.getInt("id");
                int book_id = resultSet.getInt("book_id");
                int student_id = resultSet.getInt("student_id");
                Timestamp start = resultSet.getTimestamp("start_time");
                Timestamp end = resultSet.getTimestamp("end_time");
                System.out.println("id:"+id+", book_id:"+book_id+", student_id:"
                        +student_id+", start_time:"+start+", end_time:"+end);
            }
            // 释放
            resultSet.close();
            statement.close();
        }
        resultSet2.close();
        statement2.close();
        resultSet1.close();
        statement1.close();
        connection.close();

    }
}
